"""
比对及修复表
"""
import csv
import datetime
from io import StringIO

from flask import request, jsonify, Response, render_template

from tzq.database_compare_system import dcs_bp
from tzq.database_compare_system.models.JobTableColumnRepare import JobTableColumnRepare
from tzq.extensions import db


# 根据比对任务，比对并修复表，layui多条件分页查询接口
@dcs_bp.route('/api/jobTableColumnRepares', methods=['GET'])
def get_job_table_column_repares():
    page = int(request.args.get('page', 1))
    limit = int(request.args.get('limit', 10))

    # 获取搜索参数
    comp_task_name = request.values.get("comp_task_name")
    schema = request.values.get("schema")
    table_name = request.values.get("table_name")
    column_name = request.values.get("column_name")
    total_check_result = request.values.get("total_check_result")
    table_name_check_result = request.values.get("table_name_check_result")
    column_name_check_result = request.values.get("column_name_check_result")
    data_type_check_result = request.values.get("data_type_check_result")
    data_default_check_result = request.values.get("data_default_check_result")
    pkey_check_result = request.values.get("pkey_check_result")
    nullable_check_result = request.values.get("nullable_check_result")
    table_comment_check_result = request.values.get("table_comment_check_result")
    column_comment_check_result = request.values.get("column_comment_check_result")

    # 基础查询
    query = JobTableColumnRepare.query

    # 应用搜索条件
    if comp_task_name:
        query = query.filter(JobTableColumnRepare.comp_task_name.ilike(f'%{comp_task_name}%'))
    if schema:
        query = query.filter(JobTableColumnRepare.schema.ilike(f'%{schema}%'))
    if table_name:
        query = query.filter(JobTableColumnRepare.table_name.ilike(f'%{table_name}%'))
    if column_name:
        query = query.filter(JobTableColumnRepare.column_name.ilike(f'%{column_name}%'))
    if total_check_result:
        query = query.filter(JobTableColumnRepare.total_check_result.ilike(f'%{total_check_result}%'))
    if table_name_check_result:
        query = query.filter(JobTableColumnRepare.table_name_check_result.ilike(f'%{table_name_check_result}%'))
    if column_name_check_result:
        query = query.filter(JobTableColumnRepare.column_name_check_result.ilike(f'%{column_name_check_result}%'))
    if data_type_check_result:
        query = query.filter(JobTableColumnRepare.data_type_check_result.ilike(f'%{data_type_check_result}%'))
    if data_default_check_result:
        query = query.filter(
            JobTableColumnRepare.data_default_check_result.ilike(f'%{data_default_check_result}%'))
    if pkey_check_result:
        query = query.filter(JobTableColumnRepare.pkey_check_result.ilike(f'%{pkey_check_result}%'))
    if nullable_check_result:
        query = query.filter(JobTableColumnRepare.nullable_check_result.ilike(f'%{nullable_check_result}%'))
    if table_comment_check_result:
        query = query.filter(
            JobTableColumnRepare.table_comment_check_result.ilike(f'%{table_comment_check_result}%'))
    if column_comment_check_result:
        query = query.filter(
            JobTableColumnRepare.column_comment_check_result.ilike(f'%{column_comment_check_result}%'))

    # 获取排序参数
    sort_field = request.args.get('field', 'creation_date')
    sort_order = request.args.get('order', 'desc')

    # 构建排序
    if hasattr(JobTableColumnRepare, sort_field):
        if sort_order == 'asc':
            order_by = getattr(JobTableColumnRepare, sort_field).asc()
        else:
            order_by = getattr(JobTableColumnRepare, sort_field).desc()
        query = query.order_by(order_by)
    else:
        query = query.order_by(JobTableColumnRepare.job_table_column_repare_id.desc())

    # 分页查询
    pagination = query.paginate(page=page, per_page=limit, error_out=False)

    # 构建Layui要求的JSON格式
    return jsonify({
        'code': 0,
        'msg': '',
        'count': pagination.total,
        'data': [dataInfo.to_dict() for dataInfo in pagination.items]
    })


@dcs_bp.route('/api/exportJobTableColumnRepare', methods=['GET', 'POST'])
def export_job_table_column_repare():
    """ 【模型比对修复】数据导出 """
    # 获取搜索参数 , POST方法 用 form.get
    comp_task_name = request.values.get("comp_task_name")
    schema = request.values.get("schema")
    table_name = request.values.get("table_name")
    column_name = request.values.get("column_name")
    total_check_result = request.values.get("total_check_result")
    table_name_check_result = request.values.get("table_name_check_result")
    column_name_check_result = request.values.get("column_name_check_result")
    data_type_check_result = request.values.get("data_type_check_result")
    data_default_check_result = request.values.get("data_default_check_result")
    pkey_check_result = request.values.get("pkey_check_result")
    nullable_check_result = request.values.get("nullable_check_result")
    table_comment_check_result = request.values.get("table_comment_check_result")
    column_comment_check_result = request.values.get("column_comment_check_result")

    # 基础查询
    query = JobTableColumnRepare.query

    # 应用搜索条件
    if comp_task_name:
        query = query.filter(JobTableColumnRepare.comp_task_name.ilike(f'%{comp_task_name}%'))
    if schema:
        query = query.filter(JobTableColumnRepare.schema.ilike(f'%{schema}%'))
    if table_name:
        query = query.filter(JobTableColumnRepare.table_name.ilike(f'%{table_name}%'))
    if column_name:
        query = query.filter(JobTableColumnRepare.column_name.ilike(f'%{column_name}%'))
    if total_check_result:
        query = query.filter(JobTableColumnRepare.total_check_result.ilike(f'%{total_check_result}%'))
    if table_name_check_result:
        query = query.filter(JobTableColumnRepare.table_name_check_result.ilike(f'%{table_name_check_result}%'))
    if column_name_check_result:
        query = query.filter(JobTableColumnRepare.column_name_check_result.ilike(f'%{column_name_check_result}%'))
    if data_type_check_result:
        query = query.filter(JobTableColumnRepare.data_type_check_result.ilike(f'%{data_type_check_result}%'))
    if data_default_check_result:
        query = query.filter(JobTableColumnRepare.data_default_check_result.ilike(f'%{data_default_check_result}%'))
    if pkey_check_result:
        query = query.filter(JobTableColumnRepare.pkey_check_result.ilike(f'%{pkey_check_result}%'))
    if nullable_check_result:
        query = query.filter(JobTableColumnRepare.nullable_check_result.ilike(f'%{nullable_check_result}%'))
    if table_comment_check_result:
        query = query.filter(
            JobTableColumnRepare.table_comment_check_result.ilike(f'%{table_comment_check_result}%'))
    if column_comment_check_result:
        query = query.filter(
            JobTableColumnRepare.column_comment_check_result.ilike(f'%{column_comment_check_result}%'))

    # 获取排序参数
    sort_field = request.args.get('field', 'job_table_column_repare_id')
    sort_order = request.args.get('order', 'desc')

    # 构建排序
    if hasattr(JobTableColumnRepare, sort_field):
        if sort_order == 'asc':
            order_by = getattr(JobTableColumnRepare, sort_field).asc()
        else:
            order_by = getattr(JobTableColumnRepare, sort_field).desc()
        query = query.order_by(order_by)
    else:
        query = query.order_by(JobTableColumnRepare.job_table_column_repare_id.desc())

    # 分页查询
    query = query.paginate(page=1, per_page=1000000, error_out=False)

    # # 创建 Excel 工作簿和工作表
    # wb = Workbook()
    # ws = wb.active
    # ws.title = "比对任务表模型修复"
    # 创建内存文件对象
    output = StringIO()
    output.write('\ufeff')  # 添加UTF-8 BOM头

    # 创建CSV写入器
    writer = csv.writer(output, quoting=csv.QUOTE_ALL)

    # 写入表头
    headers = ['ID', '比对任务ID', '比对任务名称', 'schema', '表名', '字段名', '汇总', '检查表', '表1', '表2', '表3',
               '表4',
               '表5', '表6', '表7', '检查字段', '字段1', '字段2', '字段2修复脚本', '字段3', '字段3修复脚本', '字段4',
               '字段4修复脚本', '字段5', '字段5修复脚本', '字段6', '字段6修复脚本', '字段7', '字段7修复脚本',
               '字段类型',
               '类型1', '类型2', '类型2修复脚本', '类型3', '类型3修复脚本', '类型4', '类型4修复脚本', '类型5',
               '类型5修复脚本', '类型6', '类型6修复脚本', '类型7', '类型7修复脚本', '查默认值', '默认值1', '默认值2',
               '默认值2修复脚本', '默认值3', '默认值3修复脚本', '默认值4', '默认值4修复脚本', '默认值5',
               '默认值5修复脚本',
               '默认值6', '默认值6修复脚本', '默认值7', '默认值7修复脚本', '查主键', '主键1', '主键2', '主键2修复脚本',
               '主键3', '主键3修复脚本', '主键4', '主键4修复脚本', '主键5', '主键5修复脚本', '主键6', '主键6修复脚本',
               '主键7', '主键7修复脚本', '查是空', '是空1', '是空2', '是空2修复脚本', '是空3', '是空3修复脚本', '是空4',
               '是空4修复脚本', '是空5', '是空5修复脚本', '是空6', '是空6修复脚本', '是空7', '是空7修复脚本',
               '查表注释',
               '表注释1', '表注释2', '表注释2修复脚本', '表注释3', '表注释3修复脚本', '表注释4', '表注释4修复脚本',
               '表注释5',
               '表注释5修复脚本', '表注释6', '表注释6修复脚本', '表注释7', '表注释7修复脚本', '查列注释', '列注释1',
               '列注释2', '列注释2修复脚本', '列注释3', '列注释3修复脚本', '列注释4', '列注释4修复脚本', '列注释5',
               '列注释5修复脚本', '列注释6', '列注释6修复脚本', '列注释7', '列注释7修复脚本', '更新时间']
    writer.writerow(headers)

    # 写入数据
    for item in query:
        row = [
            item.job_table_column_repare_id,
            item.comp_task_id,
            item.comp_task_name,
            item.schema,
            item.table_name,
            item.column_name,
            item.total_check_result,
            item.table_name_check_result,
            item.table_name1,
            item.table_name2,
            item.table_name3,
            item.table_name4,
            item.table_name5,
            item.table_name6,
            item.table_name7,
            item.column_name_check_result,
            item.column_name1,
            item.column_name2,
            item.column_name2_repair_script,
            item.column_name3,
            item.column_name3_repair_script,
            item.column_name4,
            item.column_name4_repair_script,
            item.column_name5,
            item.column_name5_repair_script,
            item.column_name6,
            item.column_name6_repair_script,
            item.column_name7,
            item.column_name7_repair_script,
            item.data_type_check_result,
            item.data_type1,
            item.data_type2,
            item.data_type2_repair_script,
            item.data_type3,
            item.data_type3_repair_script,
            item.data_type4,
            item.data_type4_repair_script,
            item.data_type5,
            item.data_type5_repair_script,
            item.data_type6,
            item.data_type6_repair_script,
            item.data_type7,
            item.data_type7_repair_script,
            item.data_default_check_result,
            item.data_default1,
            item.data_default2,
            item.data_default2_repair_script,
            item.data_default3,
            item.data_default3_repair_script,
            item.data_default4,
            item.data_default4_repair_script,
            item.data_default5,
            item.data_default5_repair_script,
            item.data_default6,
            item.data_default6_repair_script,
            item.data_default7,
            item.data_default7_repair_script,
            item.pkey_check_result,
            item.pkey1,
            item.pkey2,
            item.pkey2_repair_script,
            item.pkey3,
            item.pkey3_repair_script,
            item.pkey4,
            item.pkey4_repair_script,
            item.pkey5,
            item.pkey5_repair_script,
            item.pkey6,
            item.pkey6_repair_script,
            item.pkey7,
            item.pkey7_repair_script,
            item.nullable_check_result,
            item.nullable1,
            item.nullable2,
            item.nullable2_repair_script,
            item.nullable3,
            item.nullable3_repair_script,
            item.nullable4,
            item.nullable4_repair_script,
            item.nullable5,
            item.nullable5_repair_script,
            item.nullable6,
            item.nullable6_repair_script,
            item.nullable7,
            item.nullable7_repair_script,
            item.table_comment_check_result,
            item.table_comment1,
            item.table_comment2,
            item.table_comment2_repair_script,
            item.table_comment3,
            item.table_comment3_repair_script,
            item.table_comment4,
            item.table_comment4_repair_script,
            item.table_comment5,
            item.table_comment5_repair_script,
            item.table_comment6,
            item.table_comment6_repair_script,
            item.table_comment7,
            item.table_comment7_repair_script,
            item.column_comment_check_result,
            item.column_comment1,
            item.column_comment2,
            item.column_comment2_repair_script,
            item.column_comment3,
            item.column_comment3_repair_script,
            item.column_comment4,
            item.column_comment4_repair_script,
            item.column_comment5,
            item.column_comment5_repair_script,
            item.column_comment6,
            item.column_comment6_repair_script,
            item.column_comment7,
            item.column_comment7_repair_script,
            item.creation_date]
        writer.writerow(row)

    date_string = datetime.datetime.now().strftime("%Y%m%d-%H%M%S")

    # 将工作簿写入 BytesIO 流
    # output = BytesIO()
    # wb.save(output)
    # output.seek(0)

    # 使用 Response 返回 Excel 文件
    # return Response(
    #     output.getvalue(),
    #     mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    #     headers={
    #         "Content-Disposition": "attachment; filename=" + date_string + "-job_table_column_repare.xlsx"
    #     }
    # )
    # 创建响应对象
    response = Response(
        output.getvalue(),
        mimetype="text/csv; charset=utf-8",
        headers={
            "Content-disposition": "attachment; filename=" + date_string + "-job_table_column_repare.csv",
            "Content-type": "text/csv; charset=utf-8"
        }
    )
    return response


@dcs_bp.route('/api/jobTableColumnRepareGetTaskNames', methods=['GET'])
def job_table_column_repare_get_task_names():
    """
    从PostgreSQL获取 comp_task_name 下拉框的数据，提供json数据的api接口
    【模型比对修复】查询页面 的 【任务名】下拉框 展示
    """
    try:
        # 查询去重后的 source 字段值
        distinct_comp_task_names = db.session.query(JobTableColumnRepare.comp_task_name).distinct().all()
        # 提取结果（因为返回的是元组列表）
        comp_task_names_data = [{"comp_task_name": item[0]} for item in distinct_comp_task_names]  # 提取元组中的第一个元素
        return jsonify({"code": 0, "msg": "", "count": len(comp_task_names_data), "data": comp_task_names_data})
    except Exception as e:
        print("Database error:", e)
        return jsonify({"code": -1, "msg": str(e), "count": 0, "data": None})


@dcs_bp.route('/jobTableColumnRepareCodeView2', methods=['GET', 'POST'])
def job_table_column_repare_code_view2():
    if request.method == "GET":
        id_string = request.args.get("id")
        # print("id_string = " + id_string)
        instance_tmp = JobTableColumnRepare.query.get_or_404(str(id_string))
        # print("id_string = " + id_string)
    return render_template('dcs_code_viewer.html', content=instance_tmp.compare_scripts_of_source2)
